﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVLABELData : BaseFunction
    {
        
        public MVLABELData()
            : base()
        {

        }
        public MVLABELData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWASTAMM(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string ART = dwABPOS["ARTICLE"].ToString().Trim();
            string SUPART = dwABPOS["SUPART"].ToString().Trim();
            string DESIGN = dwABPOS["DESIGN"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select trim(waart) as VAAART,wabez as des,wafbrtu*0.1 as totwidth, "
                    + " wafbrtl*0.1 as valwidth,wafgrqm,wafgrlfd,trim(wafbem) as yarn, "
                    + " wabind,walohna,b.lsname1 as supp, " 
                    + " (SELECT AGG(DECODE(A,'XXX','',A)||B) FROM (  "
                    + "  SELECT DISTINCT C,A,MAX(B)OVER(PARTITION BY C,A) AS B FROM ( "
                    + "  SELECT DISTINCT C,A,STRAGG(B)OVER(PARTITION BY C,A ORDER BY A,D DESC) B FROM ( "
                    + "  SELECT DISTINCT TRIM(SUBSTR(STUELISAPD,15,20)) AS C,    "
                    + "  DECODE(TRIM(SUBSTR(STUELIDATEN,55,2)),'R','XXX','FL','FACE:','GS','GROUND:') AS A,   "
                    + "  TO_NUMBER(SUBSTR(STUELIDATEN,1,4))*0.1||'%'||TRIM(STUELISASB) AS B , SUBSTR(STUELIDATEN,1,4) AS D   "
                    + "  FROM STUELI WHERE SUBSTR(STUELIDATEN,55,2) IN ('R ','FL','GS')  "
                    + "  )))WHERE C=TRIM(A.WAART)) AS comp ,waneud, "
                    + " (SELECT AGG(TRIM(TXTKTEXT)) AS REMARK FROM TXTIND C,TXTKON D "
                    + " WHERE C.TXTIMANDANT = D.TXTKMANDANT AND C.TXTIPIND = D.TXTKPIND AND TXTITXTART = 'WET' "
                    + " AND TRIM(SUBSTR(TXTISIND,15,12)) =TRIM(A.WAART)) AS remark, "
                    + " (SELECT DISTINCT STRAGG(DISTINCT TRIM(f.vafdess)) "
                    + "  OVER(PARTITION BY V.VAAART)||DECODE(TRIM(V.VAAKURZBEZ),'.','',','||TRIM(V.VAAKURZBEZ)) "
                    + " FROM VAART V,VAFARBEN F "
                    + "  WHERE V.VAAMANDANT = F.VAFMANDANT AND V.VAAKOLL = F.VAFKOLL "
                    + "  AND V.VAAART = F.VAFART AND A.WAART=v.vaadart  )  AS DESIGN "
                    + " from wastamm a,liefdat b "
                    + " where a.wamandant = b.lsmandant and a.waliefnr = b.lsliefnr "
                    + " and waart like '"+ART+"%' AND A.WALOHNA LIKE '%"+SUPART+"%' "
                    + " and A.WAART IN ( SELECT v.vaadart FROM VAART V,VAFARBEN F "
                    + "  WHERE V.VAAMANDANT = F.VAFMANDANT AND V.VAAKOLL = F.VAFKOLL "
                    + "  AND V.VAAART = F.VAFART "
                    + "  AND (TRIM(V.Vaakurzbez) = '" + DESIGN + "' or TRIM(f.vafdess) = '" + DESIGN + "' OR '" + DESIGN + "'='XXXXXXXXXX')) "
                    + " order by 1 ";
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["des"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["des"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["yarn"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["yarn"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["wabind"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["wabind"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["walohna"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["walohna"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["supp"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["supp"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["remark"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["remark"] = value;
                    }
                }
                /*DataSet set = new DataSet();
                foreach (DataRow row in Result.Tables[0].Rows)
                {
                    string STR = GetCOMP(row);
                    //string REMARK = GetREMARK(row);
                    row["comp"] = STR;
                    //row["remark"] = REMARK;
                }*/
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetLableContent(string art,string color)
        {
            DataSet Result;
            DataSet Return=new DataSet();
            string TableName = "ARTICLE";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select VAAART,(CASE WHEN TRIM(VAAKURZBEZ) = '.' THEN VAAART "
                    + " WHEN TRIM(VAAKURZBEZ) = TRIM(VAAART) THEN VAAART  WHEN TRIM(VAAKURZBEZ) IS NULL THEN VAAART "
                    + "    ELSE TRIM(VAAART)||'('||TRIM(VAAKURZBEZ)||')' END ) AS ART,  VAABEZ,COMP,WIDTH,WEIGHT FROM ( "
                    + " select VAAART,NVL((SELECT TRIM(VAFDESS) FROM VAFARBEN "
                    + " WHERE TRIM(VAFART)=TRIM(VAAART) AND TRIM(VAFFARBE)= :FARBE ) "
                    + " ,VAAKURZBEZ) AS  VAAKURZBEZ,VAABEZ,'' AS COMP, "
                    + " VAABRTVON*0.01||' '||VAAMEBRT||'(CUT)-'||VAABRTBIS*0.01||' '||VAAMEBRT AS WIDTH, "
                    + " (SELECT WAFGRLFD||' G/M' FROM WASTAMM "
                    + " WHERE TRIM(WAART)= TRIM(VAAART))||' '||(SELECT WAFGRQM||' G/M2' FROM WASTAMM "
                    + " WHERE TRIM(WAART)= TRIM(VAAART))AS WEIGHT " 
                    + " from vaart where trim(vaaart) = :article ) ";

                orlReadCommand.Parameters.Add("article", OracleType.NVarChar).Value = art;
                orlReadCommand.Parameters.Add("FARBE", OracleType.NVarChar).Value = color;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["VAABEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["VAABEZ"] = value;
                    }
                }
                DataSet set = new DataSet();
                foreach (DataRow row in Result.Tables[0].Rows)
                {
                    string STR= GetCOMP(row);
                    row["COMP"] = STR;
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public string GetCOMP(DataRow dwABPOS)
        {
            string COMP = "";

            string TableName = "COMP";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT TRIM(SUBSTR(STUELISAPD,15,20)) AS C,  "
                    + " DECODE(TRIM(SUBSTR(STUELIDATEN,55,2)),'R','XXX','FL','FACE:','GS','GROUND:') AS A, "
                    + " TO_NUMBER(SUBSTR(STUELIDATEN,1,4))*0.1||'%'||TRIM(STUELISASB) AS B ,SUBSTR(STUELIDATEN,1,4) AS D "
                    + " FROM STUELI WHERE SUBSTR(STUELIDATEN,55,2) IN ('R ','FL','GS')  "
                    + " AND TRIM(SUBSTR(STUELISAPD,15,20)) = :ARTICLE "
                    + " ORDER BY C,A,D DESC  "
                    ;
                orlReadCommand.Parameters.Add("ARTICLE", OracleType.NVarChar).Value = dwABPOS["VAAART"].ToString().Trim();
                
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    string A = "";
                    string B = "";
                    int i = 0;
                    foreach (DataRow dwTemp in dsResult.Tables[TableName].Rows)
                    {
                        A = dwTemp["A"].ToString();
                        B = dwTemp["B"].ToString();
                        string COM = "";
                        if (!COMP.Contains(A))
                        {
                            if(i==0)
                            {
                                COM = A + B;
                                i = i + 1;
                            }
                            else
                            {
                                COMP = COMP.Substring(0, COMP.Length - 1);
                                COM = Environment.NewLine;
                                COM += A + B;
                            }
                        }
                        else
                        {
                            COM = B;
                        }
                        COMP += COM+",";
                        //COMP += dwTemp["A"].ToString()+dwTemp["B"].ToString();
                        //COMP += Environment.NewLine;
                        
                    }
                    i = 0;
                    COMP = COMP.Substring(0, COMP.Length - 1);
                    COMP = COMP.Replace("XXX"," ");
                    EventDispatcher.Instance.RaiseStatus("Composition of " + dwABPOS["VAAART"].ToString().Trim() + " is " + COMP, StatusEventType.RunningProgress);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return COMP;
        }
        public string GetREMARK(DataRow dwABPOS)
        {
            string remark = "";
            string TableName = "REMARK";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT TRIM(TXTKTEXT) AS REMARK "
                    + " FROM TXTIND A,TXTKON B "
                    + " WHERE A.TXTIMANDANT = B.TXTKMANDANT AND A.TXTIPIND = B.TXTKPIND "
                    + " AND TXTITXTART = 'WET'  "
                    + " AND TRIM(SUBSTR(TXTISIND,15,12)) = :ARTICLE " 
                    ;
                orlReadCommand.Parameters.Add("ARTICLE", OracleType.NVarChar).Value = dwABPOS["VAAART"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dwTemp in dsResult.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        remark += value + Environment.NewLine;
                    }
                    EventDispatcher.Instance.RaiseStatus("remark of " + dwABPOS["VAAART"].ToString().Trim() + " is " + remark, StatusEventType.RunningProgress);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return remark;
        }
        public DataSet BuildMVLABELData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWASTAMM(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}